package com.sjzd.meeting.dao;

import com.sjzd.meeting.pojo.MeetingPublish;
import com.sjzd.meeting.pojo.MeetingPublishRank;
import com.sjzd.meeting.pojo.MeetingType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * MeetingPublishDAO继承基类
 */
@Mapper
public interface MeetingPublishDAO extends MyBatisBaseDao<MeetingPublish, String> {
    // 根据会议召开日期的特定字符串查询当天最大的会议编号
    @Select("select max(mcode) from meeting_publish where LEFT(mcode,8) = #{mtime}")
    String selectMaxMcodeByMtime(String mtime);

    @Select("select * from meeting_publish where uid = #{uid} order by mcode desc")
    List<MeetingPublish> selectPublishMeetingByUid(Integer uid);

    @Select("select * from meeting_publish")
    List<MeetingPublish> selectAll();

    MeetingPublish selectPublishMeetingDetailById(String id);

    @Select("select u.id as uid, wx.nickname as nickname, wx.headimgurl as headimgurl, sum(case when grab.match_status=1 then 1 else 0 end) as succCount, count(*) as sumCount" +
            " from meeting_publish pub left join meeting_grab grab on pub.id = grab.mid" +
            " left join user u on pub.uid = u.id" +
            " left join wx_user wx on u.wxid = wx.id" +
            " group by u.id order by succCount desc, sumCount desc")
    List<MeetingPublishRank> selectPublishMeetingRank();

    @Select("select count(id) from meeting_publish where datediff(now(), create_time) = 1")
    Integer countYesterdayPublished();

    @Select("select count(id) from meeting_publish  where id not in (select mid from meeting_grab where match_status = 1)")
    Integer countTodayCanGrab();

    @Select("select mt.tname tname, count(mt.id) count from meeting_type mt" +
            " left join meeting_publish pub on pub.tid = mt.id and pub.id not in (select mid from meeting_grab where match_status = 1)" +
            " group by mt.id")
    List<MeetingType> countEveryTypeCanGrab();

    @Select("select count(id) from meeting_grab where datediff(now(), create_time) = 1 and match_status = 1")
    Integer countYesterdaySuccMatched();
}